﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DAL;
using DTO;
using System.Drawing;
using System.IO;
using System.Text.RegularExpressions;


namespace BLL
{
    public class StaffBLL
    {
        private static int staffid = 0;
        HospitalDataContext DB = new HospitalDataContext();
        public IQueryable SelectAll()
        {
            return DB.Staffs.Select( n=> new{
                n.StaffID,
                Name = n.FirstName+" "+n.LastName,
                n.Address,
                n.Sex,
                n.DateOfBirth,
                n.Tel,
                n.NIN,
                n.ImageProfile,
                n.Status
            }
            ).AsQueryable();

        }
        public List<Staff> SelectAll2()
        {
            return DB.Staffs.ToList<Staff>();

        }
        public IQueryable SelectDesc()
        {
            return DB.Staffs.Select(s => new
                {
                    StaffID = s.StaffID,
                    Name = s.FirstName + " " + s.LastName,
                    Address = s.Address,
                    Sex = s.Sex,
                    DateOfBirth = s.DateOfBirth,
                    Tel = s.Tel,
                    NIN = s.NIN,
                    ImageProfile = s.ImageProfile,
                    Status = s.Status

                }).OrderByDescending(s => s.StaffID).AsQueryable();

        }
        public string Insert(StaffDTO staffDTO)
        {
            try
            {
                Staff staff = new Staff();
                staff.StaffID = (staffid++).ToString();
                staff.FirstName = staffDTO.FirstName;
                staff.LastName = staffDTO.Lastname;
                staff.Address = staffDTO.Address;
                staff.Sex = staffDTO.Sex;
                staff.DateOfBirth = staffDTO.Dob;
                staff.Tel = staffDTO.Tel;
                staff.NIN = staffDTO.Nin;
                try
                {
                    Image picture = staffDTO.PictureProfile;
                    MemoryStream ms = new MemoryStream();
                    picture.Save(ms, System.Drawing.Imaging.ImageFormat.Png);
                    staff.ImageProfile = ms.GetBuffer();
                }
                catch (Exception ex)
                {
                }
                staff.Status = staffDTO.Status;

                DB.Staffs.InsertOnSubmit(staff);
                DB.SubmitChanges();
                return "Thanh cong";

            }
            catch (Exception ex)
            {
                return ex.Message;
            }

        }
        public string Update(StaffDTO staffDTO)
        {
            try
            {
                Staff staff = DB.Staffs.Where(s => s.StaffID == staffDTO.StaffId).FirstOrDefault();

                staff.FirstName = staffDTO.FirstName;
                staff.LastName = staffDTO.Lastname;
                staff.Address = staffDTO.Address;
                staff.Sex = staffDTO.Sex;
                staff.DateOfBirth = staffDTO.Dob;
                staff.Tel = staffDTO.Tel;
                staff.NIN = staffDTO.Nin;
                try
                {
                    Image picture = staffDTO.PictureProfile;
                    MemoryStream ms = new MemoryStream();
                    picture.Save(ms, System.Drawing.Imaging.ImageFormat.Png);
                    staff.ImageProfile = ms.GetBuffer();
                }
                catch (Exception ex)
                {
                }

                DB.SubmitChanges();
                return "success";

            }
            catch (Exception ex)
            {
                return ex.Message;
            }

        }
        public List<Staff> Search(string key)
        {
            return DB.Staffs.Where(s => s.StaffID.Contains(key) || s.FirstName.Contains(key) || s.LastName.Contains(key)).ToList();
        }
        public static bool IsNumber(string value)
        {
            // Return true if this is a number.
            int number1;
            return int.TryParse(value, out number1);
        }
        public IQueryable SearchIDName(string key)
        {
            int a = 5;

            if (key.Length == 4 && key.ToCharArray()[0].ToString().ToUpper().Equals("S") && IsNumber(key.Substring(1, 3))&&(Convert.ToInt32(key.Substring(1, 3)).GetType() == a.GetType()))
            {
                return DB.Staffs.Select(s => new
                {
                    StaffID = s.StaffID,
                    Name = s.FirstName + " " + s.LastName,
                    Address = s.Address,
                    Sex = s.Sex,
                    DateOfBirth = s.DateOfBirth,
                    Tel = s.Tel,
                    NIN = s.NIN,
                    ImageProfile = s.ImageProfile,
                    Status = s.Status
                }).Where(s => s.StaffID==key).AsQueryable();
            }
            else
            {
                return DB.Staffs.Select(s => new
                {
                    StaffID = s.StaffID,
                    Name = s.FirstName + " " + s.LastName,
                    Address = s.Address,
                    Sex = s.Sex,
                    DateOfBirth = s.DateOfBirth,
                    Tel = s.Tel,
                    NIN = s.NIN,
                    ImageProfile = s.ImageProfile,
                    Status = s.Status
                }).Where(s => s.Name.Contains(key)).AsQueryable();
            }
        }

        public IQueryable SearchType(string key)
        {
            return DB.Staffs.Join(
                DB.Qualifications,
                a => a.StaffID,
                b => b.StaffID,
                (a, b) => new
                {
                    StaffID = a.StaffID,
                    Name = a.FirstName + " " + a.LastName,
                    Address = a.Address,
                    Sex = a.Sex,
                    DateOfBirth = a.DateOfBirth,
                    Tel = a.Tel,
                    NIN = a.NIN,
                    ImageProfile = a.ImageProfile,
                    Status = a.Status,
                    Type = b.Type,
                    Date = b.Date,
                    Institution = b.Institution
                }
                ).Where( s => s.Type.Contains(key) || s.Institution.Contains(key)).AsQueryable();
        }

        public IQueryable SearchExperience(string key)
        {
            return DB.Staffs.Join(
                DB.Experiences,
                a => a.StaffID,
                b => b.StaffID,
                (a, b) => new
                {
                    StaffID = a.StaffID,
                    Name = a.FirstName + " " + a.LastName,
                    Address = a.Address,
                    Sex = a.Sex,
                    DateOfBirth = a.DateOfBirth,
                    Tel = a.Tel,
                    NIN = a.NIN,
                    ImageProfile = a.ImageProfile,
                    Status = a.Status,
                    Position = b.Position,
                    b.Organization
                }
                ).Where(s => s.Position.Contains(key) || s.Organization.Contains(key)).AsQueryable();
        }

        public List<JobDTO> ReceiveJob(string staffID)
        {
            return DB.Jobs.Join(
                DB.Positions,
                a => a.PositionID,
                b => b.PositionID,
                (a, b) => new JobDTO
                {
                    StaffId = a.StaffID,
                    PositionName = b.Position1,
                    Hours = Convert.ToDouble(a.HoursWeek),
                    CurrentSalary = Convert.ToInt32(a.CurrentSalary),
                    SalaryScale = a.SalaryScale,
                    Paid = a.Paid,
                    Contract = a.Contract,
                    StartDate = Convert.ToDateTime(a.StartDate)
                }
                ).Where(s => s.StaffId == staffID).ToList();
                
        }
        public List<string> ReceiveWard(string staffID)
        {
            return DB.Jobs.Join(
                DB.StaffOfWards,
                a => a.JobID,
                b => b.JobID,
                (a, b) => new
                {
                    a.StaffID,
                    a.StartDate,
                    FinishDate1 = a.FinishDate,
                    FinishDate2 = b.FinishDate,
                    b.WardID
                }
                ).Where(s=>s.StaffID==staffID && s.FinishDate1==null && s.FinishDate2 ==null).Select(n=>n.WardID).ToList();

        }

        public IQueryable SelectNewStaff()
        {
            return DB.Staffs.Select(n => new
            {
                n.StaffID,
                Name=n.FirstName+" "+n.LastName,
                n.Address,
                n.Sex,
                n.DateOfBirth,
                n.Tel,
                n.NIN,
                n.Status
            }).Where(s=>s.Status != "Not Work").OrderBy(v=>v.Status).AsQueryable();
        }

        public IQueryable LaythongtinStaff()
        {
            return DB.Staffs.Join(
                DB.Jobs,
                a => a.StaffID,
                b => b.StaffID,
                (a, b) => new
                {
                    a.StaffID,
                    Name = a.FirstName + " " + a.LastName,
                    a.Address,
                    a.Sex,
                    a.DateOfBirth,
                    a.Tel,
                    a.NIN,
                    a.Status,
                    b.FinishDate,
                    b.PositionID
                }
                ).Join(
                DB.Positions,
                c => c.PositionID,
                d => d.PositionID,
                (c, d) => new
                {
                    c.StaffID,
                    c.Name,
                    c.Address,
                    c.Sex,
                    c.DateOfBirth,
                    c.Tel,
                    c.NIN,
                    c.Status,
                    c.FinishDate,
                    Position=d.Position1
                }
                ).Where(s=>s.FinishDate==null).Distinct().OrderByDescending(s=>s.Status).AsQueryable();
        }

        public IQueryable SearchIDNamePosition(string key)
        {
            int k = 5;

            if (key.Length == 4 && key.ToCharArray()[0].ToString().ToUpper().Equals("S") && IsNumber(key.Substring(1, 3)) && (Convert.ToInt32(key.Substring(1, 3)).GetType() == k.GetType()))
            {
                return DB.Staffs.Join(
                DB.Jobs,
                a => a.StaffID,
                b => b.StaffID,
                (a, b) => new
                {
                    a.StaffID,
                    Name = a.FirstName + " " + a.LastName,
                    a.Address,
                    a.Sex,
                    a.DateOfBirth,
                    a.Tel,
                    a.NIN,
                    a.Status,
                    b.FinishDate,
                    b.PositionID
                }
                ).Join(
                DB.Positions,
                c => c.PositionID,
                d => d.PositionID,
                (c, d) => new
                {
                    c.StaffID,
                    c.Name,
                    c.Address,
                    c.Sex,
                    c.DateOfBirth,
                    c.Tel,
                    c.NIN,
                    c.Status,
                    c.FinishDate,
                    Position = d.Position1
                }
                ).Where(s => s.FinishDate == null && s.StaffID==key).Distinct().OrderByDescending(s => s.Status).AsQueryable();
            }
            else
            {
                return DB.Staffs.Join(
                DB.Jobs,
                a => a.StaffID,
                b => b.StaffID,
                (a, b) => new
                {
                    a.StaffID,
                    Name = a.FirstName + " " + a.LastName,
                    a.Address,
                    a.Sex,
                    a.DateOfBirth,
                    a.Tel,
                    a.NIN,
                    a.Status,
                    b.FinishDate,
                    b.PositionID
                }
                ).Join(
                DB.Positions,
                c => c.PositionID,
                d => d.PositionID,
                (c, d) => new
                {
                    c.StaffID,
                    c.Name,
                    c.Address,
                    c.Sex,
                    c.DateOfBirth,
                    c.Tel,
                    c.NIN,
                    c.Status,
                    c.FinishDate,
                    Position = d.Position1
                }
                ).Where(s => s.FinishDate == null &&( s.Name.Contains(key)|| s.Position.Contains(key))).Distinct().OrderByDescending(s => s.Status).AsQueryable();
            }
        }

        public bool DeleteQua(string quaid )
        {
            try
            {
                Qualification qua = DB.Qualifications.Where(s => s.QualificationID == quaid).FirstOrDefault();
                DB.Qualifications.DeleteOnSubmit(qua);
                DB.SubmitChanges();
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }
        public bool DeleteExp(string expid)
        {
            try
            {
                Experience exp = DB.Experiences.Where(s => s.ExperienceID==expid).FirstOrDefault();
                DB.Experiences.DeleteOnSubmit(exp);
                DB.SubmitChanges();
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }




    }
}
